/**
 * Created by Administrator on 2017/8/31 0031.
 */
var mysql = require('./mysql/mysql');
var utils = require('../../shared/utils');
var disUserDao = module.exports;

/**
 * Get user info by phoneNo
 * @param {String} phoneNo
 * @param {function} cb
 */
disUserDao.getUserById = function (openId,ref1,regChannel,cb){

    var sql = 'SELECT * FROM dis_user WHERE openId = ?';
    var args = [openId];
    //console.log(' getUserById:%s,referee:%s,regChannel:%s',uid,referee,regChannel);
    mysql.query(sql, args, function(err, user){
        if(err) {
            console.log("disUserDao.getUserById: ", sql, args, err, user);
            cb(1, null);
            return;
        }
        if(!user.length && openId && openId != null && openId !='null') {               //不存在则插入,默认官渠
            if(!regChannel || regChannel == 'null') regChannel = 'official';
            disUserDao.createUser(openId,ref1,regChannel, function (err,user) {
                if(err){
                    cb(1,null);
                    console.log(' create user:%s,uid:%s',JSON.stringify(user),openId);
                    return;
                }
                cb(null,{uid:user.insertId,coin:0,isNew:true});
            });
        }else {
            cb(null,user[0]);
        }
    });
};


/**
 * create a new user
 */
disUserDao.createUser = function (openId, ref1,regChannel,cb) {

    var sql = 'insert into dis_user (openId,ref1,regChannel,regDate) values (?, ?, ?, ?)';
    var args = [openId,ref1,regChannel,utils.toMysqlFormat(new Date())];

    mysql.insert(sql, args, function(err, res) {

        if (err) {
            console.log("disUserDao.createUser: ", sql, args, err, res);
            cb(1, null);
            return;
        }
        else {
            cb(null, res);
        }
    });
};

disUserDao.getUserByUid = function (uid,cb) {
    var sql = 'SELECT * FROM dis_user WHERE uid = ?';
    mysql.query(sql,uid, function (err,res) {
       if(err){
           cb(1,null);
       } else {
           cb(null,res[0]);
       }
    });
};

/**
 * 6级关系
 * @param refs
 */
disUserDao.updateUserRef = function (refs) {
    var sql = 'UPDATE dis_user SET ref2 = ?,ref3 = ?,ref4 = ?,ref5 = ?,ref6 = ? WHERE openId = ?; ';
    mysql.query(sql, refs, function(err){
        if(err){
            console.log('updateUserRef err:%s, args:%s ,sql:%s',err,JSON.stringify(refs),sql);
        }
    });
};

/**
 * 2级关系
 * @param refs
 */
disUserDao.setDisRef2 = function (ref2,openId) {
    var sql = 'UPDATE dis_user SET ref2 = ? WHERE openId = ?; ';
    mysql.query(sql, [ref2,openId], function(err){
        if(err){
            console.log('updateUserRef err:%s, args:%s ,sql:%s',err,JSON.stringify(ref2,openId),sql);
        }
    });
};

/**
 * 查找uid推荐的玩家
 * @param uid
 * @param cb
 */
disUserDao.getRefereeNum = function (openId,cb) {
    var sql1 = 'SELECT COUNT(openId) ref1Num FROM dis_user WHERE ref1 = ?;';
    var sql2 = 'SELECT COUNT(openId) ref2Num FROM dis_user WHERE ref2 = ? OR ref3 = ? OR ref4 = ? OR ref5 = ? OR ref6 = ?;';
    mysql.query(sql1, openId, function(err, ref1Num){
        if(err){
            console.log('getRefereeNum 1 err:%s, openId:%s,sql1:%s',err,openId,sql1);
            cb(1,null);
        } else {
            mysql.query(sql2, [openId,openId,openId,openId,openId], function(err, ref2Num){
                if(err){
                    console.log('getRefereeNum 2 err:%s, openId:%s',err,openId);
                    cb(null,{
                        ref1Num:ref1Num[0].ref1Num,
                        ref2:0
                    });
                }else {
                    cb(null,{
                        ref1Num:ref1Num[0].ref1Num,
                        ref2Num:ref2Num[0].ref2Num
                    });
                }
            });
        }
    });
};

disUserDao.getRef1info =function(openId,cb){
    var sql = 'SELECT * FROM dis_user WHERE ref1 = ? ORDER BY vip DESC, coin DESC;';
    mysql.query(sql, openId, function(err, refInfo){
       if(err){
           cb(1,null);
       } else {
           cb(null,refInfo);
       }
    });
};


disUserDao.getRef2info =function(openId,cb){
    var sql = 'SELECT * FROM dis_user WHERE ref2 = ? OR ref3 = ? OR ref4 = ? OR ref5 = ? OR ref6 = ? ORDER BY vip DESC, coin DESC;';
    mysql.query(sql,  [openId,openId,openId,openId,openId], function(err, refInfo){
        if(err){
            cb(1,null);
        } else {
            cb(null,refInfo);
        }
    });
};


disUserDao.getCashList = function (openId,cb) {
    var sql = 'SELECT cash,addDate FROM dis_cash WHERE openId = ?';
    mysql.query(sql, [openId], function(err, list){
        if(err){
            cb(1,null);
        } else {
            cb(null,list);
        }
    });
};

/**
 * 获取佣金记录
 * @param uid
 * @param cb
 */
disUserDao.getComiList = function (openId,cb) {
    var sql = 'SELECT coin,status,addDate FROM dis_commission WHERE openId = ? ORDER BY `addDate` DESC';
    mysql.query(sql, [openId], function(err, list){
        if(err){
            cb(1,null);
        } else {
            cb(null,list);
        }
    });
};

/**
 * 获取积分记录
 * @param uid
 * @param cb
 */
disUserDao.getPoinList = function (uid,cb) {
    var sql = 'SELECT * FROM dis_poin WHERE uid = ? ORDER BY `addDate` DESC;';
    mysql.query(sql, uid, function(err, list){

        if(err){
            cb(1,null);
            console.log('getPoinList err:%s,sql:%s,list:%s',err,sql,JSON.stringify(list));
        } else {
            cb(null,list);
        }
    });
};
/**
 * 提取未提现佣金
 * @param uid
 * @param cb
 */
disUserDao.extractCoin = function (openId,cb) {
    var sql1 = 'SELECT SUM(coin) AS totalCoin FROM dis_commission WHERE `status` = 0 AND openId = ?';
    var sql2 = 'UPDATE dis_commission SET `status` = 1 WHERE openId = ?';
    mysql.query(sql1, [openId], function(err, res){
        if(err){
            cb(1,null);
            console.log('extractCoin sql1 error:',err,sql1);
        } else {
            mysql.query(sql2,[openId], function (err) {
                if(err){
                    cb(1,null);
                    console.log('extractCoin sql2 error:',err,sql2);
                }else {
                    var totalCoin = res[0].totalCoin ? res[0].totalCoin:0;
                    cb(null,totalCoin);
                }
            })
        }
    });
};

disUserDao.increaseUserCoin = function (openId,coin,cb) {
    var sql = 'update dis_user set coin = coin+? where openId = ?';
    mysql.query(sql, [coin,openId], function(err){
        if(err){
            console.log('updateUserCoin err:%s,data:%s',err,JSON.stringify([coin,openId]));
            cb(1,null);
        }else {
            cb(null,1);
        }
    });
};


disUserDao.increaseUserPoin = function (openId,poin,cb) {
    var sql = 'update dis_user set poin = poin+? where openId = ?';
    mysql.query(sql, [poin,openId], function(err){
        if(err){
            console.log('updateUserCoin err:%s,data:%s',err,JSON.stringify([poin,openId]));
            cb(1,null);
        }else {
            cb(null,1);
        }
    });
};

disUserDao.increaseUserPoinByUid = function (uid,poin,cb) {
    var sql = 'update dis_user set poin = poin+? where uid = ?';
    mysql.query(sql, [poin,uid], function(err){
        if(err){
            console.log('updateUserCoin err:%s,data:%s',err,JSON.stringify([poin,uid]));
            cb(1,null);
        }else {
            cb(null,1);
        }
    });
};

disUserDao.setUserVip = function (openId,cb) {
    var sql = 'update dis_user set vip = 1 where openId = ?';
    mysql.query(sql, [openId], function(err){
        if(err){
            console.log('setUserVip err:%s,data:%s',err,JSON.stringify([openId]));
            cb(1,null);
        }else {
            cb(null,1);
        }
    });
};

disUserDao.recorderCash = function (uid,openId,coin,cash,billNo) {
    var sql = 'insert into dis_cash (uid,openId,coin,cash,billNo,addDate) values (?,?,?,?,?,?)';
    var args = [uid, openId,coin,cash,billNo,utils.toMysqlFormat(new Date())];
    mysql.query(sql,args, function(err){
        if(err) {
            console.log('recorderCash err:%s,data:%s', err, JSON.stringify(args));
        }
    });
};
disUserDao.recorderOrders = function (uid,openId,totalFee,orderId,wxOrderId,payDate,cb) {
    var sql = 'INSERT INTO dis_orders (uid,openId,totalFee,orderId,wxOrderId,payDate) VALUES (?,?,?,?,?,?)';
    mysql.query(sql, [uid,openId,totalFee,orderId,wxOrderId,payDate], function(err){
        if(err) {
            console.log('recorderOrders err:%s,data:%s', err);
            cb(1,null);
        }else {
            cb(null,1);
        }
    });
};

/**
 * 插入佣金记录
 * @param uid
 * @param cb
 */
disUserDao.addCommission = function (openId,coin,contributor) {
    var self = this;
    self.getUserById(openId,null,null, function (err,user) {
        if(!err && user){
            var sql = 'INSERT INTO dis_commission (uid,openId,coin,contributor,`addDate`) VALUES (?,?,?,?,?)';
            var args = [user.uid,openId,coin,contributor,utils.toMysqlFormat(new Date())];
            mysql.query(sql, args, function(err){
                if(err){
                    console.log('addCommission error',err,args);
                }
            });
        }
    });
};

/**
 * 记录积分操作
 * @param uid
 * @param poin
 * @param action    1:赠送 2:得到
 * @param target
 * @param cb
 */
disUserDao.recordUserPoin = function (uid,poin,action,target,cb) {

    var sql = 'insert into dis_poin(uid,poin,action,target,addDate) values (?,?,?,?,?)';
    var args = [uid,poin,action,target,utils.toMysqlFormat(new Date())];

    mysql.insert(sql, args, function(err, res) {

        if (err) {
            console.log("disUserDao.createCash: ", sql, args, err, res);
            cb(1, null);
            return;
        }
        else {
            cb(null, res);
        }
    });
};

disUserDao.getTodayCash = function (openId,cb) {
    var sql = 'SELECT SUM(cash) totalCash FROM dis_cash WHERE openId = ? And TO_DAYS(`addDate`)= TO_DAYS(NOW());';
    mysql.query(sql,openId,function (err,res) {
        if(err){
            console.log('disUserDao getTodayCash error:%s ,openId:%s',err,openId);
            cb(1,null);
        }else {
            cb(null,res[0].totalCash);
        }
    });
}

/**
 *
 * @param uid
 * @param flag  which wheel
 * @param item  which item
 */
disUserDao.recordUserAction = function (uid,openId,vip) {
    var sql = 'insert into dis_action (uid,openId,vip,addDate) values(?,?,?,?)';
    var params = [uid,openId,vip,utils.toMysqlFormat(new Date())];
    mysql.query(sql,params,function (err) {
        if(err){
            console.log('recordUserAction error:%s ,params:%s',err,params);
        }
    });
};

disUserDao.userAction = function (uid,flag,cb) {
    var sql = 'select * from user_action where uid = ? and flag = ?';
    mysql.query(sql,[uid,flag],function (err,list) {
        if(err){
            console.log('recordUserAction error:%s ,params:%s',err,[uid,flag]);
            cb(1,null);
        }else {
            cb(null,parseInt(list.length));
        }
    })
};

/********管理员后台*********/

disUserDao.getDisUser = function (cb) {
    var sql = 'SELECT * FROM `dis_user` ORDER BY vip DESC, coin DESC,poin DESC;';
    mysql.query(sql,null,function (err,list) {
        if(err){
            console.log('getAllUser error:%s ,list:%s',err,list);
            cb(1,null);
        }else {
            cb(null,list);
        }
    });
};

disUserDao.getDisOrders = function (cb) {
    var sql = 'SELECT * FROM `dis_orders` ORDER BY `payDate` DESC ;';
    mysql.query(sql,null,function (err,list) {
        if(err){
            console.log('getAllUser getDisOrders:%s ,list:%s',err,list);
            cb(1,null);
        }else {
            cb(null,list);
        }
    });
};

disUserDao.getDisCash = function (cb) {
    var sql = 'SELECT * FROM `dis_cash` ORDER BY `addDate` DESC ;';
    mysql.query(sql,null,function (err,list) {
        if(err){
            console.log('getAllUser getDisCash:%s ,list:%s',err,list);
            cb(1,null);
        }else {
            cb(null,list);
        }
    });
};

disUserDao.recorderEveryData = function () {
    var sql1 = 'SELECT COUNT(*) userNum FROM dis_user;';
    var sql2 = 'SELECT COUNT(vip) vipUser FROM dis_user WHERE vip = 1;';
    var sql3 = 'SELECT SUM(poin) poinNum FROM dis_user;';
    var sql4 = 'SELECT SUM(coin) coinNum FROM dis_user;';
    var sql5 = 'SELECT SUM(cash) cashNum FROM dis_cash;';
    var sql6 = 'SELECT IFNULL(SUM(totalFee),0) orderNum FROM dis_orders;';
    var sql7 = 'INSERT INTO dis_data(userNum,vipUser,poinNum,coinNum,cashNum,orderNum,`addDate`) VALUES(?,?,?,?,?,?,?)';
    var userNum=0,vipUser= 0,poinNum= 0,coinNum= 0,cashNum= 0,orderNum=0;
    mysql.query(sql1,null, function (err1,res1) {
        if(!err1 && res1.length>0) userNum = res1[0].userNum;

        mysql.query(sql2,null, function (err2,res2) {
            if(!err2 && res2.length>0) vipUser = res2[0].vipUser;

            mysql.query(sql3,null, function (err3,res3) {
                if(!err3 && res3.length>0) poinNum = res3[0].poinNum;

                mysql.query(sql4,null, function (err4,res4) {
                    if(!err4 && res4.length>0) coinNum = res4[0].coinNum;

                    mysql.query(sql5,null, function (err5,res5) {
                        if(!err5 && res5.length>0) cashNum = res5[0].cashNum;

                        mysql.query(sql6,null, function (err6,res6) {
                            if (!err6 && res6.length > 0) orderNum = res6[0].orderNum;

                            mysql.query(sql7,[userNum,vipUser,poinNum,coinNum,cashNum,orderNum,utils.toMysqlFormat(new Date())], function (err7) {
                                if (err7) console.log('recorderEveryData err:%s ,args:%s', err7, [userNum, vipUser, poinNum, coinNum, cashNum, orderNum]);
                            });

                        });

                    });
                });
            });
        });
    });
};

/********渠道管理*********/

disUserDao.getDisChannelWorth = function (channel,cb) {
    var sql1 = 'SELECT SUM(coin) AS totalCoin  FROM `dis_user` WHERE regChannel= ?;';
    var sql2 = 'SELECT SUM(O.totalFee) AS totalFee FROM `dis_user` U,(SELECT `uid`,IFNULL(SUM(totalFee),0) AS totalFee FROM `dis_orders` WHERE `status` = 0  GROUP BY `uid` ) AS O WHERE U.uid = O.uid AND U.`regChannel` = ? ;';
    var sql3 = 'SELECT SUM(C.cash) AS cash FROM `dis_user` U,(SELECT `uid`,IFNULL(SUM(cash),0) AS cash FROM `dis_cash` WHERE `status` = 0 AND DATE_FORMAT(addDate,"%Y-%m-%d") < CURDATE() GROUP BY `uid` ) AS C WHERE U.uid = C.uid AND U.`regChannel` = ?;';
    mysql.query(sql1,[channel],function (err,res1) {
        if (err) {
            console.log('getChannelWorth1 err:%s ,res1:%s', err, res1);
            cb(1, null);
        } else {
            mysql.query(sql2, [channel], function (err, res2) {
                if (err) {
                    console.log('getChannelWorth2 err:%s ,res1:%s', err, res2);
                    cb(1, null);
                } else {
                    mysql.query(sql3, [channel], function (err, res3) {
                        if (err) {
                            console.log('getChannelWorth3 err:%s ,res1:%s', err, res3);
                            cb(1, null);
                        } else {
                            cb(err,{totalCoin:res1[0].totalCoin,totalFee:res2[0].totalFee,totalRmb:res3[0].cash});
                        }
                    });
                }
            });
        }
    });
};

disUserDao.getDisChannelUser = function (channel,cb) {
    var sql = 'SELECT * FROM `dis_user` where regChannel= ? ORDER BY vip DESC, coin DESC,poin DESC;';
    mysql.query(sql,[channel],function (err,lists) {
        if(err){
            console.log('getChannelUser err:%s ,users:%s',err,lists);
            cb(1,null);
        }else {
            cb(null,lists);
        }
    });
};

disUserDao.getDisChannelOrders = function (channel,cb) {
    var sql = 'SELECT U.uid,U.openId,U.regChannel,U.coin,O.totalFee FROM `dis_user` U,(SELECT `uid`,IFNULL(SUM(totalFee),0) as totalFee from `dis_orders` where `status`= 0  GROUP BY `uid` ) as O where U.uid = O.uid AND U.`regChannel` = ? ORDER BY U.`coin` DESC;';
    mysql.query(sql,[channel],function (err,lists) {
        if(err){
            console.log('getChannelOrders err:%s ,users:%s',err,lists);
            cb(1,null);
        }else {
            cb(null,lists);
        }
    });
};


disUserDao.getDisChannelCash = function (channel,cb) {
    var sql = 'SELECT U.uid,U.openId,U.regChannel,U.coin,C.cash FROM `dis_user` U,(SELECT `uid`,IFNULL(SUM(cash),0) as cash from `dis_cash` where `status`= 0  GROUP BY `uid` ) as C where U.uid = C.uid AND U.`regChannel` = ? ORDER BY U.`coin` DESC ;';
    mysql.query(sql,[channel],function (err,lists) {
        if(err){
            console.log('getChannelCash err:%s ,users:%s',err,lists);
            cb(1,null);
        }else {
            cb(null,lists);
        }
    });
};

disUserDao.listDisChannel = function (openId,channel,coinNum,orderNum,cashNum,dividedNum,cb) {
    var sql = 'INSERT INTO dis_channel(openId,channel,coinNum,orderNum,cashNum,dividedNum,`addDate`) VALUES (?,?,?,?,?,?,?)';
    var args = [openId,channel,coinNum,orderNum,cashNum,dividedNum,utils.toMysqlFormat(new Date())];
    mysql.query(sql,args, function (err) {
        if(err){
            console.log('listChannel err:%s',err);
            cb(1,null);
        }else {
            cb(null,1);
        }
    });
};
disUserDao.channelDisHistory = function (channel,cb) {
    var sql = 'SELECT * FROM dis_channel WHERE channel =? ORDER BY `addDate` DESC;';

    mysql.query(sql,[channel], function (err,res) {
        if(err){
            console.log('listChannel err:%s',err);
            cb(1,null);
        }else {
            cb(null,res);
        }
    });
};

disUserDao.balanceDisChannel = function (channel,openid,cb) {
    var sql1 = 'UPDATE dis_orders O,dis_user U SET O.status=1 WHERE O.status = 0 and O.openId=U.openId AND U.regChannel= ? AND DATE_FORMAT(O.payDate,"%Y-%m-%d") < CURDATE();';
    var sql2 = 'UPDATE dis_cash C,dis_user U SET C.status=1 WHERE C.status = 0 and C.openId=U.openId AND U.regChannel= ? AND DATE_FORMAT(C.addDate,"%Y-%m-%d") < CURDATE();';
    var orderChange =0,cashChange = 0;
    mysql.query(sql1,[channel],function (err,res1) {
        if(err){
            console.log('balanceChannel1 err:%s ,users:%s',err,JSON.stringify(res1));
            cb(1,null);
        }else {
            orderChange = res1.changedRows;
            mysql.query(sql2,[channel],function (err,res2) {
                if(err){
                    console.log('balanceChannel2 err:%s ,users:%s',err,JSON.stringify(res2));
                    cb(1,null);
                    return;
                }
                cashChange = res2.changedRows;
                if(orderChange == 0 && 0 == cashChange ){
                    cb(null,{cashChange:cashChange,orderChange:orderChange});
                }else {
                    var sql3 = 'INSERT INTO dis_chan_bal (openId,channel,cashNum,orderNum,`addDate`) VALUES (?,?,?,?,?)';
                    var args = [openid,channel,cashChange,orderChange,utils.toMysqlFormat(new Date())];
                    mysql.query(sql3,args, function (err) {
                        if(err){
                            console.log('addChannelBan err:%s',err);
                            cb(1,null);
                        }else {
                            cb(null,{cashChange:cashChange,orderChange:orderChange});
                        }
                    });
                }

            });

        }
    });
};

/********插入测试数据*********/
disUserDao.createUserR1R2 = function (uid, referee1,ref2,cb) {

    var sql = 'insert into user (uid,referee1,referee2,regDate) values (?,?,?,?)';
    var args = [uid, referee1,ref2,utils.toMysqlFormat(new Date())];

    mysql.insert(sql, args, function(err, res) {

        if (err) {
            console.log("disUserDao.createUserR1R2: ", sql, args, err, res);
            cb(1, null);
            return;
        }
        else {
            cb(null, res);
        }
    });
};

disUserDao.createCom = function (uid,openId,coin,contributor,cb) {

    var sql = 'insert into dis_commission (uid,openId,coin,contributor,addDate) values (?,?,?,?,?)';
    var args = [uid, openId,coin,contributor,utils.toMysqlFormat(new Date())];

    mysql.insert(sql, args, function(err, res) {

        if (err) {
            console.log("disUserDao.createCom: ", sql, args, err, res);
            cb(1, null);
            return;
        }
        else {
            cb(null, res);
        }
    });
};

disUserDao.createCash = function (uid,openId,coin,billNo,cb) {

    var sql = 'insert into dis_cash (uid,openId,coin,cash,billNo,addDate) values (?,?,?,?,?,?)';
    var args = [uid, openId,coin,coin,billNo,utils.toMysqlFormat(new Date())];

    mysql.insert(sql, args, function(err, res) {

        if (err) {
            console.log("disUserDao.createCash: ", sql, args, err, res);
            cb(1, null);
            return;
        }
        else {
            cb(null, res);
        }
    });
};

disUserDao.createOrders = function (uid,openId,totalFee,orderId,wxOrderId,cb) {

    var sql = 'insert into dis_Orders(uid,openId,totalFee,orderId,wxOrderId,payDate) values (?,?,?,?,?,?)';
    var args = [uid,openId,totalFee,orderId,wxOrderId,utils.toMysqlFormat(new Date())];

    mysql.insert(sql, args, function(err, res) {

        if (err) {
            console.log("disUserDao.createCash: ", sql, args, err, res);
            cb(1, null);
            return;
        }
        else {
            cb(null, res);
        }
    });
};
disUserDao.createPoin = function (uid,poin,action,target,cb) {

    var sql = 'insert into dis_poin(uid,poin,action,target,addDate) values (?,?,?,?,?)';
    var args = [uid,poin,action,target,utils.toMysqlFormat(new Date())];

    mysql.insert(sql, args, function(err, res) {

        if (err) {
            console.log("disUserDao.createCash: ", sql, args, err, res);
            cb(1, null);
            return;
        }
        else {
            cb(null, res);
        }
    });
};

